

-- =============================================
-- Create table function (TF)
-- =============================================
IF EXISTS (SELECT * 
	   FROM   sysobjects 
	   WHERE  name = N'f_gettraderptype')
	DROP FUNCTION f_gettraderptype
GO

CREATE FUNCTION f_gettraderptype
	(@traderid int, @parentid int)
RETURNS @traderproducttype TABLE 
	(id int, 
	 name varchar(60),
         subcode varchar(60),
         code varchar(40),
         pid int,
         taxrate decimal(18,8),
         deftaxrate decimal(18,8),
         pause bit,
         imgindex int)
AS
BEGIN
  declare @id int,
	 @name varchar(60),
         @subcode varchar(60),
         @code varchar(40),
         @pid int,
         @taxrate decimal(18,8),
         @deftaxrate decimal(18,8),
         @pause bit,
         @imgindex int
  
  if @traderid<>-1 and @parentid=-1
  begin
    declare bomtrader_cur cursor local forward_only for
    select distinct pt.id,pt.name,pt.subcode,pt.code,pt.pid,pt.taxrate,pt.deftaxrate,pt.pause,pt.imgindex
      from td_producttype pt,td_productbomtrader t,td_product p
      where p.p_id=t.productid and p.producttypeid=pt.id and t.traderid=@traderid
    open bomtrader_cur
    fetch next from bomtrader_cur into @id,@name,@subcode,@code,@pid,@taxrate,@deftaxrate,@pause,@imgindex
    while @@fetch_status=0
    begin 
      insert into @traderproducttype values(@id,@name,@subcode,@code,@pid,@taxrate,@deftaxrate,@pause,@imgindex)
      insert into @traderproducttype select * from dbo.f_gettraderptype(-1,@pid)
      fetch next from bomtrader_cur into @id,@name,@subcode,@code,@pid,@taxrate,@deftaxrate,@pause,@imgindex
    end
    close bomtrader_cur
    deallocate bomtrader_cur
  end
  else if @traderid=-1 and @parentid<>-1
  begin
    declare traderptype_cur cursor local forward_only for 
    select distinct pt.id,pt.name,pt.subcode,pt.code,pt.pid,pt.taxrate,pt.deftaxrate,pt.pause,pt.imgindex
      from td_producttype pt where id=@parentid
    
    open traderptype_cur
    fetch next from traderptype_cur into @id,@name,@subcode,@code,@pid,@taxrate,@deftaxrate,@pause,@imgindex
    while @@fetch_status=0
    begin 
      insert into @traderproducttype values(@id,@name,@subcode,@code,@pid,@taxrate,@deftaxrate,@pause,@imgindex)
      insert into @traderproducttype select * from dbo.f_gettraderptype(-1,@pid)
      fetch next from traderptype_cur into @id,@name,@subcode,@code,@pid,@taxrate,@deftaxrate,@pause,@imgindex
    end
    close traderptype_cur
    deallocate traderptype_cur
  end
	RETURN 
END
GO
--select * from td_producttype
-- =============================================
-- Example to execute function
-- =============================================
--select * from td_trader
--SELECT * FROM dbo.table_function_name(7,-1)
GO

